package com.bw.dao;

import com.bw.bean.Sugar;
import com.bw.bean.Sugar2;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface SugarDao {
    @Select("select p.`name`,sum(o.final_total_amount) `value` from order_info o \n" +
            "LEFT JOIN base_province p ON o.province_id=p.id \n" +
            "GROUP BY p.`name`")
    List<Sugar> getList1();

    @Select("select t.`name`,sum(o.final_total_amount) `value` from order_info o \n" +
            "LEFT JOIN order_detail d ON o.id=d.order_id\n" +
            "LEFT JOIN sku_info s ON s.id=d.sku_id   \n" +
            "LEFT JOIN base_trademark t ON s.tm_id=t.id \n" +
            "where t.`name` is not null \n" +
            "GROUP BY t.`name`  ")
    List<Sugar> getList2();

    @Select("select c1.`name`,sum(o.final_total_amount) `value` from order_info o \n" +
            "LEFT JOIN order_detail d ON o.id=d.order_id \n" +
            "LEFT JOIN sku_info s ON s.id=d.sku_id \n" +
            "LEFT JOIN base_category3 c3 ON c3.id=s.category3_id\n" +
            "LEFT JOIN base_category2 c2 ON c2.id=c3.category2_id\n" +
            "LEFT JOIN base_category1 c1 ON c1.id=c2.category1_id\n" +
            "where c1.`name` is not null\n" +
            "GROUP BY c1.`name`")
    List<Sugar> getList3();

    @Select("select * from\n" +
            "(select count(*) 'name' from user_info where TIMESTAMPDIFF(month,create_time,NOW()) >= 1) a,\n" +
            "(select count(*) 'value' from user_info where TIMESTAMPDIFF(month,create_time,NOW()) < 1) b\n")
    List<Sugar> getList4();

    @Select("select CONCAT(SUBSTR(u.birthday,3,1),'0后') `name`,\n" +
            "sum(o.final_total_amount) `value`\n" +
            "from user_info u \n" +
            "LEFT JOIN order_info o ON u.id=o.user_id\n" +
            "where o.final_total_amount is not null\n" +
            "GROUP BY CONCAT(SUBSTR(u.birthday,3,1),'0后')")
    List<Sugar> getList5();

    @Select("select yys `name`,sum(final_total_amount) `value` from order_info where yys is not null group by yys \n")
    List<Sugar> getList6();

    @Select("select t.`name`,\n" +
            "IF(u.gender='F','男','女') `gender`,\n" +
            "sum(o.final_total_amount) `value`\n" +
            "from order_info o \n" +
            "LEFT JOIN user_info u ON u.id=o.user_id\n" +
            "LEFT JOIN order_detail d ON o.id=d.order_id\n" +
            "LEFT JOIN sku_info s ON s.id=d.sku_id\n" +
            "LEFT JOIN base_trademark t ON t.id=s.tm_id\n" +
            "where t.`name` is not null\n" +
            "GROUP BY  t.`name`,IF(u.gender='F','男','女')")
    List<Sugar2> getList7();

    @Select("select t.`name`,sum(o.final_total_amount) `value` from order_info o \n" +
            "LEFT JOIN order_detail d ON o.id=d.order_id\n" +
            "LEFT JOIN sku_info s ON s.id=d.sku_id   \n" +
            "LEFT JOIN base_trademark t ON s.tm_id=t.id \n" +
            "where t.`name` is not null \n" +
            "GROUP BY t.`name`  ")
    List<Sugar> getList8();

    @Select("call sp3;")
    double getList9();

    @Select("select p.spu_name `name`,sum(o.final_total_amount) `value` from order_info o \n" +
            "LEFT JOIN order_detail d ON o.id=d.order_id\n" +
            "LEFT JOIN sku_info s ON s.id=d.sku_id \n" +
            "LEFT JOIN spu_info p ON p.id=s.spu_id\n" +
            "where p.spu_name is not null\n" +
            "GROUP BY p.spu_name")
    List<Sugar> getList10();
}
